<?php

require_once "vo/UserMovie.php";

/**
 * Data Mapper pour la table t_movie
 *
 * @package App_Model_Map
 */
class App_Model_MovieMapper
{
    /**
     * @var Zend_Db_Adapter_Pdo_Abstract
     */
    private $_db;

    private $_logger;

    public function __construct() {
        $this->_db = Zend_Db_Table::getDefaultAdapter();
        $this->_tables = Adapter::getTables();
        $this->_logger = Adapter::getLogger();
    }

    /**
     * Retourne la liste des films de l'utilisateur
     * @param int $userId ID utilisateur
     * @return array of App_Model_Vo_UserMovie
     */
    public function getMoviesByUserID($userId) {
        $sql = "SELECT
                    um.idUser AS userId,
                    um.format AS format,
                    um.quality AS quality,
                    um.languages AS languages,
                    um.subtitles AS subtitles,
                    um.watched AS watched,
                    m.idMovie AS movieId,
                    m.name AS name,
                    m.nameFr AS nameFr,
                    m.ratingAverage AS ratingAvg,
                    DATE_FORMAT(m.dateOut, '%Y') AS dateOut,
                    um.rating AS userRating
                FROM {$this->_tables->user_movie} um
                JOIN {$this->_tables->movie} m ON m.idMovie = um.idMovie
                WHERE um.idUser = ?
                ORDER BY m.name ASC";
        $binds = array($userId);
        $results = $this->_db->fetchAll($sql, $binds);

        $objects = array();
        foreach ($results as $result)
            $objects[] = new App_Model_Vo_UserMovie($result);
        return $objects;
    }

    /**
     *
     * @param unknown $apiName
     * @param unknown $idList
     * @return unknown
     */
    public function getMoviesByUserByApiIdList($userId, $apiName, $idList) {
        $sql = "SELECT mo.idMovieApi AS idMovieApi
                FROM {$this->_tables->movie} mo
                JOIN {$this->_tables->user_movie} um ON um.idMovie = mo.idMovie
                WHERE mo.api = ?
                    AND mo.idMovieApi IN ($idList)
                    AND um.idUser = ?";
        $binds = array($apiName, $userId);
        $results = $this->_db->fetchAll($sql, $binds);

        $toReturn = array();
        foreach ($results as $re)
            $toReturn[] = $re["idMovieApi"];
        return $toReturn;
    }

    /**
     * Retourne la liste des film par nom
     *
     * @param array $titles     titre de film
     *
     * @return Zend_Db_Select
     */
//     public function getMoviesByTitle($titles)
//     {
//         $binds = array();
//         $condition = '1';

//         foreach ($titles as $title) {
//             $condition .= " AND m.name COLLATE latin1_general_ci LIKE ?";
//             $condition .= " AND m.nameFr COLLATE latin1_general_ci LIKE ?";
//             $binds[] = "%$title%";
//             $binds[] = "%$title%";
//         }

//         $sql = "SELECT
//                     m.idMovie AS idMovie,
//                     m.idMovieApi AS api_idMovie,
//                     m.api AS apiId,
//                     m.ratingAverage AS ratingAverage
//                 FROM {$this->_tables->movie} m
//                 WHERE $condition
//                 ORDER BY m.name ASC";
//         $results = $this->_db->fetchAll($sql, $binds);

//         $objects = array();
//         foreach ($results as $result)
//             $objects[] = new App_Model_Vo_MovieByTitle($result);
//         return $objects;
//     }

    /**
     * Retourne la liste des film de l'utilisateur
     *
     * @param int $idUser ID utilisateur
     *
     * @return Zend_Db_Select
     */
//     public function getFriendMovies($idUser, $idFriend) {

//         $movieColumns = array(
//                 "t_movie.idMovie",
//                 "t_movie.name",
//                 "t_movie.nameFr",
//                 "t_movie.rating AS avgRating",
//                 "DATE_FORMAT(t_movie.dateOut, '%Y') AS dateOut",
//         );
//         $friendMovieColumns = array(
//                 "um1.rating AS friendRating",
//                 "um1.format",
//                 "um1.quality",
//                 "um1.languages",
//                 "um1.subtitles",
//         );
//         $movieShareColumns = array(
//                 "IF(share.idUser IS NULL, 'no', 'yes') AS asked",
//         );
//         $userMovieColumns = array(
//                 "IF(um2.idUser IS NULL, 'no', 'yes') AS owned",
//         );

//         $database = Zend_Db_Table::getDefaultAdapter();
//         $query = $database->select()
//         ->from('rt_user_movie AS um1', $friendMovieColumns)
//         ->join('t_movie', 't_movie.idMovie = um1.idMovie', $movieColumns)
//         ->joinLeft('rt_movie_share AS share', "share.idUser = $idUser AND share.idFriend = $idFriend AND share.idMovie = um1.idMovie", $movieShareColumns)
//         ->joinLeft('rt_user_movie AS um2', "um2.idUser = $idUser AND um2.idMovie = um1.idMovie", $userMovieColumns)

//         ->where('um1.idUser = ?', $idFriend)

//         ->order('t_movie.name ' . Zend_Db_Select::SQL_ASC);

//         $resultSet = $database->query($query);

//         // echo "$idUser, $idFriend\n";
//         // var_dump($resultSet);die;

//         $movies = array();
//         foreach ($resultSet as $row) {

//             // var_dump($row);//die;

//             $movieVO = new App_Model_Vo_Movie();
//             $this->populate($row, $movieVO);
//             $movies[] = $movieVO;
//         }

//         // die;
//         // var_dump($movies);die;

//         return $movies;
//     }


    /**
     * Retourne les derniers films ajouté de l'utilisateur
     *
     * @param int $idUser ID utilisateur
     * @param int $limit  nb results
     *
     * @return Zend_Db_Select
     */
    public function getLastMoviesAddedByUserID($idUser, $limit) {
        $sql = "SELECT m.name AS name,
                    m.ratingAverage AS ratingAvg,
                    m.ratingNumber AS ratingNb,
                    DATE_FORMAT(um.added, '%d/%m/%Y') AS added
                FROM {$this->_tables->movie} m
                JOIN {$this->_tables->user_movie} um ON um.idMovie = m.idMovie
                WHERE um.idUser = ?
                ORDER BY um.added DESC
                LIMIT $limit";
        $binds = array($idUser);
        $results = $this->_db->fetchAll($sql, $binds);
        //var_dump($results);die;
        return $results;
    }

    public function getLastMoviesAdded($limit) {
//         $sql = "SELECT m.name AS name,
//                     m.ratingAverage AS ratingAvg,
//                     m.ratingNumber AS ratingNb,
//                     DATE_FORMAT(um.added, '%d/%m/%Y') AS added
//                 FROM {$this->_tables->movie} m
//                 JOIN {$this->_tables->user_movie} um ON um.idMovie = m.idMovie
//                 ORDER BY um.added DESC
//                 LIMIT $limit";
        $sql = "SELECT
                    mo.name AS name,
                    DATE_FORMAT(mo.added, '%d/%m/%Y') AS added
                FROM mediaglobe.t_movie mo
                ORDER BY mo.added DESC
                LIMIT 10";
//         $this->_logger->debug("=== getLastMoviesAdded ===============");
//         $this->_logger->debug($sql);
        $results = $this->_db->fetchAll($sql);
        //var_dump($results);die;
        return $results;
    }

//     public function OLD_getLastMoviesAddedByUserID($idUser, $limit)
//     {
//         $database = Zend_Db_Table::getDefaultAdapter();

//         $movieColumns = array(
//                 'name',
//                 't_movie.rating AS avgRating',
//         );

//         $linkColumns = array(
//                 'rt_user_movie.rating AS myRating',
//                 "DATE_FORMAT(rt_user_movie.added, '%d/%m/%Y') AS added",
//         );

//         $query = $database->select()
//         ->from('t_movie', $movieColumns)
//         ->join('rt_user_movie', 'rt_user_movie.idMovie = t_movie.idMovie', $linkColumns)
//         ->where('rt_user_movie.idUser = ' . $idUser)
//         ->order('rt_user_movie.added ' . Zend_Db_Select::SQL_DESC)
//         ->limit($limit);

//         $resultSet = $database->query($query);

//         $entries = array();
//         foreach ($resultSet as $row) {
//             //             $movieVO = new App_Model_Vo_Movie();
//             //             $this->populate($row, $movieVO);
//             //             $entries[] = $movieVO;
//             $entries[] = array(
//                     'name' => $row['name'],
//                     'myRating' => $row['myRating'],
//                     'avgRating' => $row['avgRating'],
//                     'added' => $row['added']
//             );
//         }

//         return $entries;
//     }


    public function getBestRatedMovies($limit) {
        $sql = "SELECT m.name AS name, m.ratingAverage AS ratingAvg, m.ratingNumber AS ratingNb
                FROM {$this->_tables->movie} m
                WHERE m.ratingAverage IS NOT NULL
                ORDER BY m.ratingAverage DESC";
        $results = $this->_db->fetchAll($sql);
        //var_dump($results);die;
        return $results;
    }

//     public function OLD_getBestRatedMovies($idUser, $limit)
//     {
//         $database = Zend_Db_Table::getDefaultAdapter();

//         $movieColumns = array(
//                 'name',
//                 't_movie.rating AS avgRating',
//         );

//         $query = $database->select()
//         ->from('t_movie', $movieColumns)
//         ->where('t_movie.rating IS NOT NULL')
//         ->order('t_movie.rating ' . Zend_Db_Select::SQL_DESC)
//         ->limit($limit);

// //         $resultSet = $database->query($query);
//         $results = $database->fetchAll($query);
//         return $results;

// //         $entries = array();
// //         foreach ($resultSet as $row) {
// //             $entries[] = array(
// //                     'name' => $row['name'],
// //                     'avgRating' => $row['avgRating'],
// //             );
// //         }
// //         return $entries;
//     }

    /**
     * Retourne le nombre de films de l'utilisateur
     *
     * @param int $idUser ID utilisateur
     *
     * @return Zend_Db_Select
     */
    public function getNbMoviesByUserID($idUser) {
        $sql = "SELECT COUNT(idMovie) AS count
                FROM {$this->_tables->user_movie} um
                WHERE um.idUser = ?";
        $binds = array($idUser);
        $results = $this->_db->fetchAll($sql, $binds);
        //var_dump($results);die;
        return $results[0]['count'];
    }

//     public function movieExists($idMovie)
//     {
//         $database = Zend_Db_Table::getDefaultAdapter();

//         $columns = array(
//                 'COUNT(idMovie) as nb',
//         );

//         $query = $database->select()
//         ->from('t_movie', $columns)
//         ->where('t_movie.idMovie = ?', $idMovie);

//         $resultSet = $database->query($query); // Zend_Db_Statement_Pdo
//         $result = $resultSet->fetchAll();

//         $nbMovies = $result[0]['nb'];
//         if ($nbMovies == 1)
//             return true;
//         return false;
//     }



    public function linkExists($idUser, $idMovie) {

        $sql = "SELECT COUNT(idMovie) AS count
                FROM {$this->_tables->user_movie} um
                WHERE um.idUser = ? AND um.idMovie = ?";
        $binds = array($idUser, $idMovie);
        $results = $this->_db->fetchAll($sql, $binds);
        if ($results[0]['count'] > 0)
            return true;
        return false;
    }

    public function linkUserToMovie($idUser, $idMovie, $rating = null, $format = 0,
                                    $quality = '', $languages = 0, $subtitles = 0) {
        $sql = "INSERT INTO {$this->_tables->user_movie} (idUser, idMovie, added, rating, format,
                    quality, languages, subtitles)
                VALUES (?, ?, NOW(), ?, ?, ?, ?, ?)";
        $binds = array($idUser, $idMovie, $rating, $format, $quality, $languages, $subtitles);
        $this->_db->query($sql, $binds);
    }

    public function getMovieIdByAPI($api_name, $api_id) {
        $sql = "SELECT mo.idMovie AS movieId
                FROM {$this->_tables->movie} mo
                WHERE mo.api = ? AND mo.idMovieApi = ?";
        $binds = array($api_name, $api_id);
        $results = $this->_db->fetchAll($sql, $binds);

//         $this->_logger->debug("=== getMovieIdByAPI ===");
//         $this->_logger->debug($results);

        if (count($results) == 0)
            return -1;
        return $results[0]['movieId'];
    }

    public function addMovie($name, $nameFr, $urlPicture, $rating, $dateOut, $createdBy,
                             $description, $api_name, $api_idMovie) {
        $sql = "INSERT INTO {$this->_tables->movie} (name, nameFr, urlPicture, ratingAverage,
                    dateOut, createdBy, description, api, idMovieAPI)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        $binds = array($name, $nameFr, $urlPicture, $rating, $dateOut, $createdBy,
                       $description, $api_name, $api_idMovie);
        $results = $this->_db->query($sql, $binds);
    }

//     public function updateLinkUserMovie($idUser, $idMovie, $data) {
//         $sql = "INSERT INTO {$this->_tables->user_movie} (idUser, idMovie, added)
//         VALUES (?, ?, NOW())";
//         $binds = array($name, $nameFr, $urlPicture, $rating, $dateOut, $createdBy, $description, $api_name, $api_idMovie);
//         $results = $this->_db->query($sql, $binds);

//         $database = Zend_Db_Table::getDefaultAdapter();

//         $where = array(
//                 'idUser = ?' => $idUser,
//                 'idMovie = ?' => $idMovie,
//         );

//         $database->update('rt_user_movie', $data, $where);
//     }


    /**
     * Retourne la liste des films de l'utilisateur
     * @param int $userId ID utilisateur
     * @return array of App_Model_Vo_UserMovie
     */
    public function updateMoviesAverageRating() {
        $sql = "SELECT
                    mo.idMovie AS movieId,
                    mo.name AS movieName,
                    SUM(um.rating)/COUNT(um.rating) AS ratingAvg,
                    COUNT(um.rating) AS ratingCount
                FROM {$this->_tables->movie} mo
                JOIN {$this->_tables->user_movie} um ON um.idMovie = mo.idMovie
                GROUP BY mo.idMovie
                ORDER BY mo.idMovie";
        //$this->_db->query($sql);
        $moviesToUpdate = $this->_db->fetchAll($sql);

        $sql2 = "UPDATE t_movie mo
                SET mo.ratingAverage = ? , mo.ratingNumber = ?
                WHERE mo.idMovie = ?";

        foreach ($moviesToUpdate as $mtu) {
            $ratingAvg = $mtu['ratingAvg'];
            $ratingCount = $mtu['ratingCount'];
            $movieId = $mtu['movieId'];
            $movieName = $mtu['movieName'];
            $this->_logger->info("updating movie <$movieName> avg<$ratingAvg> count<$ratingCount>");
            $binds = array($ratingAvg, $ratingCount, $movieId);
            $this->_db->query($sql2, $binds);
        }
    }

}